﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BibleService.Lib
{
	public class _SqlHelper
	{		
		private static System.Data.SQLite.SQLiteConnection CreateConn()
		{	
			try
			{
				string _connectionString = @"Data Source=E:\BibleReader\BibleService\App_Data\Bible_Data.db3";
				var conn = new System.Data.SQLite.SQLiteConnection(_connectionString);
				return conn;
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}
		public static void Insert(string tableName, System.Collections.Specialized.NameValueCollection fieldItems)
		{
			if (fieldItems == null)
				throw new ArgumentNullException("缺少参数");
			if (fieldItems.Count < 1)
				throw new ArgumentNullException("缺少参数");

			string insertsql = string.Format("Insert Into {0} ", tableName);
			string fieldname = string.Empty;
			string fieldvalue = string.Empty;
			for (int i = 0, j = fieldItems.Count; i < j; i++)
			{
				fieldname += fieldItems.GetKey(i) + ",";
				if (string.IsNullOrEmpty(fieldItems[i]) == true)
					fieldvalue += "NULL,";
				else
					fieldvalue += string.Format("'{0}',", fieldItems[i]);
			}
			fieldname = fieldname.TrimEnd(',');
			fieldvalue = fieldvalue.TrimEnd(',');

			insertsql += string.Format("({0}) values({1})", fieldname, fieldvalue);
			_SqlHelper.Execute(insertsql);
		}
		public static T Execute<T>(string SqlString)
		{
			return Execute<T>(SqlString, null);
		}
		public static T Execute<T>(string SqlString, List<System.Data.Common.DbParameter> Parameters)
		{
			T result = default(T);
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.SQLite.SQLiteCommand comm = new System.Data.SQLite.SQLiteCommand(SqlString, conn);
				if (Parameters != null)
				{
					foreach (var paramenter in Parameters)
						comm.Parameters.Add(paramenter);
				}
				var _result = comm.ExecuteScalar();
				if (_result != null)
					result = (T)_result;
				conn.Close();
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",Sql:" + SqlString);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
			return result;
		}
		public static void Execute(string SqlString)
		{
			Execute(SqlString, null);
		}
		public static void Execute(string SqlString, List<System.Data.Common.DbParameter> Parameters)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.SQLite.SQLiteCommand comm = new System.Data.SQLite.SQLiteCommand(SqlString, conn);
				if (Parameters != null)
				{
					foreach (var paramenter in Parameters)
						comm.Parameters.Add(paramenter);
				}
				comm.ExecuteNonQuery();
				conn.Close();
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",Sql:" + SqlString);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
		}
		public static Int32 GetCount(string SqlString)
		{	
			var count=GetSingCell<System.Int64>(SqlString);
			return (Int32)count;
		}
		public static T GetSingCell<T>(string SqlString)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.SQLite.SQLiteCommand comm = new System.Data.SQLite.SQLiteCommand(SqlString, conn);
				object result = comm.ExecuteScalar();	
				T count = (T)result;
				conn.Close();
				return count;
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",sql:" + SqlString);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
		}


		public static System.Data.Common.DbDataReader GetSingRecord(string SqlString)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(SqlString, conn);
				var result = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
				if (result.Read() == false)
				{
					result.Close();
					return null;
				}
				return result as System.Data.Common.DbDataReader;
			}
			catch (Exception ex)
			{
				conn.Close();
				throw ex;
			}
		}

		public static System.Data.Common.DbDataReader GetList(string SqlString)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(SqlString, conn);
				var result = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
				return result as System.Data.Common.DbDataReader;
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",Sql:" + SqlString);
			}
		}
	}
	public class _AccessSqlHelper
	{
		private static string _connectionString;
		private static System.Data.OleDb.OleDbConnection CreateConn()
		{	
			//string ConnectionStringName = "PrisonManagerServices";
			try
			{
				//var _conn = System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionStringName];
				//if (_conn == null)
				//            throw new Exception("未配置Web.Config的数据库连接：" + ConnectionStringName);
				//_connectionString = _conn.ConnectionString;
								
				_connectionString=@"Provider=Microsoft.Jet.OleDb.4.0;Data Source=E:\BibleReader\BibleService.Import\App_Data\Bible_Data.mdb";
				System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(_connectionString);
				return conn;
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message);
			}
		}
		public static void Insert(string tableName, System.Collections.Specialized.NameValueCollection fieldItems)
		{
			if (fieldItems == null)
				throw new ArgumentNullException("缺少参数");
			if (fieldItems.Count < 1)
				throw new ArgumentNullException("缺少参数");

			string insertsql = string.Format("Insert Into {0} ", tableName);
			string fieldname = string.Empty;
			string fieldvalue = string.Empty;
			for (int i = 0, j = fieldItems.Count; i < j; i++)
			{
				fieldname += fieldItems.GetKey(i) + ",";
				if (string.IsNullOrEmpty(fieldItems[i]) == true)
					fieldvalue += "NULL,";
				else
					fieldvalue += string.Format("'{0}',", fieldItems[i]);
			}
			fieldname = fieldname.TrimEnd(',');
			fieldvalue = fieldvalue.TrimEnd(',');

			insertsql += string.Format("({0}) values({1})", fieldname, fieldvalue);
			_SqlHelper.Execute(insertsql);
		}
		public static T Execute<T>(string SqlString)
		{
			return Execute<T>(SqlString, null);
		}
		public static T Execute<T>(string SqlString, List<System.Data.Common.DbParameter> Parameters)
		{
			T result=default(T);
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(SqlString, conn);
				if (Parameters != null)
				{
					foreach (var paramenter in Parameters)
						comm.Parameters.Add(paramenter);
				}
				var _result=comm.ExecuteScalar();
				if(_result!=null)
					result=(T)_result;
				conn.Close();
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",Sql:" + SqlString);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
			return result;
		}
		public static void Execute(string SqlString)
		{
			Execute(SqlString, null);
		}
		public static void Execute(string SqlString, List<System.Data.Common.DbParameter> Parameters)
		{			
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(SqlString, conn);
				if (Parameters != null)
				{
					foreach (var paramenter in Parameters)
						comm.Parameters.Add(paramenter);
				}
				comm.ExecuteNonQuery();
				conn.Close();
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",Sql:" + SqlString);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
		}
		public static Int32 GetCount(string SqlString)
		{
			return GetSingCell<Int32>(SqlString);
		}
		public static T GetSingCell<T>(string SqlString)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.OleDb.OleDbCommand comm = new System.Data.OleDb.OleDbCommand(SqlString, conn);

				T count = (T)comm.ExecuteScalar();
				conn.Close();
				return count;
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",sql:" + SqlString);
			}
			finally
			{
				conn.Close();
				conn.Dispose();
			}
		}


		public static System.Data.Common.DbDataReader GetSingRecord(string SqlString)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(SqlString, conn);
				var result = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
				if (result.Read() == false)
				{
					result.Close();
					return null;
				}
				return result as System.Data.Common.DbDataReader;
			}
			catch (Exception ex)
			{
				conn.Close();
				throw ex;
			}
		}

		public static System.Data.Common.DbDataReader GetList(string SqlString)
		{
			var conn = CreateConn();
			try
			{
				conn.Open();
				System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(SqlString, conn);
				var result = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
				return result as System.Data.Common.DbDataReader;
			}
			catch (Exception ex)
			{
				conn.Close();
				throw new Exception(ex.Message + ",Sql:" + SqlString);
			}
		}
	}
}
